package com.smbms.dao.user;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.smbms.pojo.User;
import com.smbms.utils.CaseUtils;
import com.smbms.utils.DBUtils;
import org.junit.Test;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @Description 描述
 * @Author 羽落陌殇
 * @Date 2021/7/16 16:28
 * @Version 1.0.0
 */
public class UserDaoImpl implements UserDao {
    @Override
    public User getLoginUser(Connection connection, String userCode) throws SQLException {
        String sql = "select * from smbms_user where userCode=?";
        Object[] params = {userCode};
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        resultSet = DBUtils.executeQuery(connection, preparedStatement, resultSet, sql, params);
        if (resultSet.next()) {
            user = new User();
            // 利用反射进行数据值的匹配传递
            Method[] methods = ReflectUtil.getMethods(User.class);
            for (int i = 0; i < methods.length; i++) {
                if (methods[i].getName().startsWith("set")
                        && !methods[i].getName().equals("setAge")
                        && !methods[i].getName().equals("setUserRoleName")
                ) {
                    ReflectUtil.invoke(user, methods[i],
                            resultSet.getObject(CaseUtils.toLowerCaseFirstOne(
                                    methods[i].getName().substring(3))));
                }
            }
        }
        return user;
    }

    @Override
    public int updateUserPassword(Connection connection, int id, String userPassword) throws SQLException {
        String sql = "update smbms_user set userPassword = ? where id = ?";
        PreparedStatement preparedStatement = null;
        Object[] params = {userPassword, id};
        int result = DBUtils.executeUpdate(connection, preparedStatement, sql, params);
        DBUtils.close(connection, null, null);
        return result;
    }

    @Override
    public int getUserCount(Connection connection,String userName, int userRole) throws SQLException {
        StringBuffer sql = new StringBuffer("select count(u.id) as count from smbms_user u, smbms_role r where u.userRole = r.id");
        int count = 0;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object> list = new ArrayList<>();
        if (StrUtil.isNotEmpty(userName)) {
            sql.append(" and u.userName like ?");
            list.add("'%" + userName + "%'");
        }
        if (userRole > 0) {
            sql.append(" and u.userRole = ?");
            list.add(userRole);
        }
        Object[] params = list.toArray();
        System.out.println("SQL -> "+ sql.toString());
        resultSet = DBUtils.executeQuery(connection, preparedStatement, resultSet, sql.toString(), params);
        if (resultSet.next()) {
            count = resultSet.getInt("count");
        }
        DBUtils.close(connection, null, resultSet);
        return count;
    }

    @Override
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
        StringBuffer sql = new StringBuffer("select u.*,r.roleName as userRoleName from smbms_user u, smbms_role r where u.userRole = r.id");
        List<Object> list = new ArrayList<>();
        List<User> userList = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        if (StrUtil.isNotEmpty(userName)) {
            sql.append(" and u.userName like ?");
            list.add("%" + userName + "%");
        }
        if (userRole > 0) {
            sql.append(" and u.userRole = ?");
            list.add(userRole);
        }
        // 分页查询, 并且根据创建时间排序 // int currentPageNo, int pageSize
        sql.append(" order by creationDate DESC limit ?,?");
        currentPageNo = (currentPageNo - 1) * pageSize;
        list.add(currentPageNo);
        list.add(pageSize);

        Object[] params = list.toArray();
        resultSet = DBUtils.executeQuery(connection, preparedStatement, resultSet, sql.toString(), params);
        while (resultSet.next()) {
            user = new User();
            // 利用反射进行数据值的匹配
            Method[] methods = ReflectUtil.getMethods(User.class);
            for (int i = 0; i < methods.length; i++) {
                if (methods[i].getName().startsWith("set")
                    && !methods[i].getName().equals("setAge")) {
                    ReflectUtil.invoke(user, methods[i],
                            resultSet.getObject(CaseUtils.toLowerCaseFirstOne(
                                    methods[i].getName().substring(3))));
                }
            }
            userList.add(user);
        }
        return userList;
    }

    @Override
    public boolean userNameIsExists(Connection connection, String userCode) throws SQLException {
        String sql = "select * from smbms_user where userCode = ?";
        Object[] params = {userCode};
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        resultSet = DBUtils.executeQuery(connection, preparedStatement, resultSet, sql, params);
        if (resultSet.next()) {
            return true;
        }
        return false;
    }

    @Override
    public int createUser(Connection connection, User user) throws SQLException {
        String sql = "insert into smbms_user(userCode, userName,userPassword, gender, birthday, phone, address, userRole, createdBy,creationDate) values(?,?,?,?,?,?,?,?,?,?)";
        Object[] params = {user.getUserCode(), user.getUserName(),user.getUserPassword(), user.getGender(),
            user.getBirthday(), user.getPhone(), user.getAddress(), user.getUserRole(), user.getCreatedBy(),
                user.getCreationDate()};
        PreparedStatement preparedStatement = null;
        int result = DBUtils.executeUpdate(connection, preparedStatement, sql, params);
        return result;
    }

    @Override
    public User queryUserById(Connection connection, int id) throws SQLException {
        String sql = "select u.*,r.roleName as userRoleName from smbms_user u, smbms_role r where u.userRole = r.id and u.id = ?";
        Object[] params = {id};
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        resultSet = DBUtils.executeQuery(connection, preparedStatement, resultSet, sql, params);
        if (resultSet.next()) {
            user = new User();
            // 利用反射进行数据值的匹配
            Method[] methods = ReflectUtil.getMethods(User.class);
            for (int i = 0; i < methods.length; i++) {
                if (methods[i].getName().startsWith("set")
                        && !methods[i].getName().equals("setAge")) {
                    ReflectUtil.invoke(user, methods[i],
                            resultSet.getObject(CaseUtils.toLowerCaseFirstOne(
                                    methods[i].getName().substring(3))));
                }
            }
        }
        return user;
    }

    @Override
    public int updateUser(Connection connection, User user) throws SQLException {
        int result = 0;
        String sql = "update smbms_user set userName=?, gender=?, birthday=?, phone=?, address=?, userRole=?, modifyBy=?, modifyDate=? where id=?";
        Object[] params = {user.getUserName(), user.getGender(), user.getBirthday(), user.getPhone(),
            user.getAddress(), user.getUserRole(), user.getModifyBy(), user.getModifyDate(), user.getId()};
        PreparedStatement preparedStatement = null;
        result = DBUtils.executeUpdate(connection, preparedStatement, sql, params);
        return result;
    }

    @Override
    public int deleteUser(Connection connection, int id) throws SQLException {
        String sql = "delete from smbms_user where id=?";
        Object[] params = {id};
        PreparedStatement preparedStatement = null;
        int result = DBUtils.executeUpdate(connection, preparedStatement, sql, params);
        return result;
    }
}

